package db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Employee;
import model.Enterprise;
import model.Person;

public class EmployeeDAO {
	protected static final String TABLE = "employee";	
	private static String onSqlError = "SQL statement fault";
			
	private static final String 
		ID_FIELD = "employee",	
		PERSON_FIELD = "person_fk",
		ENTERPRISE_FIELD = "enterprise_fk",
		STRUCT_FIELD = "struct_unit_fk",
		ACTIVE_FIELD = "active";
	
	private static final String INSERT_STATEMENT_STRING = 
			"INSERT INTO "+TABLE+" " +
			"("+PERSON_FIELD+", "+ENTERPRISE_FIELD+", " +STRUCT_FIELD+", "+ACTIVE_FIELD+") " +
			"VALUES (?, ?, ?, ?)";
	
	private static final String UPDATE_STATEMENT_STRING = 
			"UPDATE "+TABLE+" SET " 
			+PERSON_FIELD+" = ?, "+ENTERPRISE_FIELD+" = ?, "+STRUCT_FIELD+" = ?, "
			+ACTIVE_FIELD+" = ? WHERE "+ID_FIELD+" = ?";
	
	private static final String DELETE_STATEMENT_STRING =
			"DELETE FROM "+TABLE+" WHERE "+ID_FIELD+" = ?";
	
	private static PreparedStatement insertStatement, updateStatement, deleteStatement;
	
	static{
		try {
			insertStatement = ImbiConnection.getConnection().prepareStatement(INSERT_STATEMENT_STRING);
			updateStatement = ImbiConnection.getConnection().prepareStatement(UPDATE_STATEMENT_STRING);
			deleteStatement = ImbiConnection.getConnection().prepareStatement(DELETE_STATEMENT_STRING);
		} catch (SQLException e) {			
			e.printStackTrace();
			System.exit(0);
		}
	}
	
	public Employee find(int id) {			
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+ID_FIELD+" = "+id);
			Employee e = extractEmployee(st.getResultSet()).get(0);							
			return e;			
		} catch (SQLException | IndexOutOfBoundsException e){
				System.out.println(onSqlError);				
				return null;
		}		
	}

	public ArrayList<Employee> findByPersonId(Person p) {			
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+PERSON_FIELD+" = "+p.getId());
			return extractEmployee(st.getResultSet());						
						
		} catch (SQLException | IndexOutOfBoundsException e){
				System.out.println(onSqlError);				
				return null;
		}		
	}

	
	private ArrayList<Employee> extractEmployee(ResultSet r) throws SQLException {
		ArrayList<Employee> list = new ArrayList<Employee>();
		while(r.next()){		
		Employee e = new Employee(
			r.getInt(1),
			r.getInt(2),
			r.getInt(3),
			r.getInt(4),
			"Y".equalsIgnoreCase(r.getString(5))
		);
		list.add(e);
		}
		return list;
	}	
	
	public void insert(Employee e){
		try{											
		    insertStatement.setInt(1, e.getPerson());
		    insertStatement.setInt(2, e.getEnterprise());		    
		    insertStatement.setInt(3, e.getStruct());
		    insertStatement.setString(4, e.isActive() ? "Y" : "N");		    	    
		    insertStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);									
		}		
	}
	
	public void update(Employee e){
		try{										
		    updateStatement.setInt(1, e.getPerson());
		    updateStatement.setInt(2, e.getEnterprise());
		    updateStatement.setInt(3, e.getStruct());
		    updateStatement.setString(4, e.isActive() ? "Y" : "N");		        
		    updateStatement.setInt(5, e.getId());
		    updateStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);									
		}			
	}
	
	public void delete(Employee e){
		try{										
		    deleteStatement.setInt(1, e.getId());		    
		    deleteStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);						
		}			
	}		
	
	public ArrayList<Employee> findAll() {
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE);								
			return extractEmployee(st.getResultSet());			
		} catch (SQLException s){
				System.out.println(onSqlError);				
				return null;
		}	
	}	
	
	public ArrayList<Employee> findAllByEnterprise(Enterprise e) {
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+ENTERPRISE_FIELD+" = "+e.getId());								
			return extractEmployee(st.getResultSet());			
		} catch (SQLException s){
				System.out.println(onSqlError);				
				return null;
		}	
	}	
}
